﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Automobile.service.Model;

namespace Automobile.service.DAL
{
    public class ProductDal
    {
        string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";
        /// <summary>
        /// 会员信息显示
        /// </summary>
        /// <returns></returns>
        public List<ProductModel> ProductFenye(int PageSize,int PageIndex)
        {
            string sql = string.Format("select top {0} * from (select ROW_NUMBER() over(order by Product.Product_Id asc)nid,Product.Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product.Product_Type_Id,Product_Type.Product_Type_Name,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from Product inner join Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id) as temp where nid>{1}", PageSize, (PageIndex - 1) * PageSize);
            SqlDataAdapter apter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            apter.Fill(table);
            List<ProductModel> list = new List<ProductModel>();
            foreach (DataRow item in table.Rows)
            {
                ProductModel p = new ProductModel();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Modelspe = item["Product_Modelspe"].ToString();
                p.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                p.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                p.Product_Type_Id = Convert.ToInt32(item["Product_Type_Id"]);
                p.Product_Barcode = item["Product_Barcode"].ToString();
                p.Product_frim = item["Product_frim"].ToString();
                p.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                p.Product_Remark = item["Product_Remark"].ToString();
                p.Product_Type_Name = item["Product_Type_Name"].ToString();
                list.Add(p);
            }
            return list;
        }
         //<summary>
         //获取信息总行数
         //</summary>
         //<returns></returns>
        public int GetProductCount()
        {
            string st = "select count(*) from Product";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(st, conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            return i;
        }
        /// <summary>
        /// 显示商品信息
        /// </summary>
        /// <returns></returns>
        public List<ProductModel> Product()
        {
            string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Id,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from Product";
            SqlDataAdapter apter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            apter.Fill(table);
            List<ProductModel> list = new List<ProductModel>();
            foreach (DataRow item in table.Rows)
            {
                ProductModel p = new ProductModel();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Modelspe = item["Product_Modelspe"].ToString();
                p.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                p.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                p.Product_Type_Id = Convert.ToInt32(item["Product_Type_Id"]);
                p.Product_Barcode = item["Product_Barcode"].ToString();
                p.Product_frim = item["Product_frim"].ToString();
                p.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                p.Product_Remark = item["Product_Remark"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public int AddProduct(ProductModel pr)
        {
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            string st = string.Format("insert into Product values('{0}','{1}',{2},{3},{4},'{5}','{6}','{7}','{8}')",pr.Product_Name,pr.Product_Modelspe,pr.Product_Bprid,pr.Product_Price,pr.Product_Type_Id,pr.Product_Barcode,pr.Product_frim,pr.Product_Unit_Id,pr.Product_Remark);
            SqlCommand cmd = new SqlCommand(st, conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            return i;
        }

        /// <summary>
        ///修改用户信息
        /// </summary>
        /// <param name="em"></param>
        /// <returns></returns>
        public int UpdateProduct(ProductModel pro)
        {
            string st = "update Product set Product_Name='" + pro.Product_Name + "',Product_Modelspe='"+pro.Product_Modelspe+"',Product_Bprid='"+pro.Product_Bprid+"',Product_Price='"+pro.Product_Price+"',Product_Type_Id='"+pro.Product_Type_Id+"',Product_Barcode='"+pro.Product_Barcode+"',Product_frim='"+pro.Product_frim+"',Product_Unit_Id='"+pro.Product_Unit_Id+"',Product_Remark='"+pro.Product_Remark+"' where Product_Id='"+pro.Product_Id+"'";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(st, conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
       
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelProduct(int id)
        {
            //连接对象
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            string sql = "delete from Product where Product_Id in (" + id + ")";
            SqlCommand cmd = new SqlCommand(sql, conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 取单个商品信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetProduct(string Product_Id)
        {
            string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Id,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from Product where Product_Id=" + Product_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }
    }
}
